# -*- coding: utf-8 -*-
import hashlib
import os
import random
import time
import uuid  # 用于生成唯一文件名
from typing import List, Optional, Dict, Any
import requests
import aiofiles  # 用于异步文件操作
import psycopg2.extras
from fastapi import FastAPI, HTTPException, status, Form, UploadFile, File
from pydantic import BaseModel

# 数据库连接模块保持不变
import db

# --- FastAPI 应用实例 ---
app = FastAPI(
    root_path="/api",
    docs_url="/api/docs",
    openapi_url="/api/openapi.json",
    root_path_in_servers=False,
    swagger_ui_parameters={"url": "/api/openapi.json"},
)
# --- 定义常量 ---
# 将上传目录定义为一个常量，方便管理
UPLOADS_DIR = r"C:\inetpub\uploads\feedback_images"
verification_codes: Dict[str, Dict[str, Any]] = {}


def generate_md5(id_str, key, time_str):
    """根据您提供的逻辑生成 MD5 签名"""
    return hashlib.md5(f"{id_str}{key}{time_str}".encode("utf-8")).hexdigest()


def send_sms(phone: str, code: str):
    """发送短信的函数，封装了您的逻辑"""
    sms_id = "Sm0005"
    # 注意：这里的 time 和 key 应该与您的短信服务商要求一致
    current_time_obj = time.localtime()
    sms_time = time.strftime("%H%M%S", current_time_obj)
    sms_key = "Zslib@Phone#hd_41"
    sms_sn_time = time.strftime("%Y%m%d%H%M%S", current_time_obj)
    sms_sn = generate_md5(sms_id, sms_key, sms_sn_time)

    url = "http://183.63.187.27/SendMess/SendAli.aspx"
    payload = {
        "id": sms_id,
        "time": sms_time,
        "sn": sms_sn,
        "total": 1,
        "phone": phone,
        "singName": "广东省立中山图书馆",
        "TemplateCode": "SMS_467420375",  # 确保这个模板能接受名为 "code" 的变量
        "data": {"code": code},
    }
    headers = {"Content-Type": "application/json"}

    try:
        response = requests.post(url, json=payload, headers=headers, timeout=10)
        response.raise_for_status()  # 如果请求失败 (如 4xx, 5xx), 则抛出异常
        sms_response = response.json()
        if sms_response.get("result") == "0" or sms_response.get("result") == "1":
            return True, "发送成功"
        else:
            return False, sms_response.get("message", "未知短信网关错误")
    except requests.exceptions.RequestException as e:
        return False, f"请求短信网关失败: {e}"


# --- Pydantic 模型 ---
class SmsRequest(BaseModel):
    phone_number: str


# --- Pydantic 模型：定义数据结构与验证 ---
# 这是 FastAPI 的核心优势。我们为请求和响应定义数据模型。


class FeedbackBase(BaseModel):
    """反馈信息的基础模型"""

    is_anonymous: bool = False
    feedback_content: str
    location: str
    image_filename: Optional[str] = None  # 可选字段
    submitter_name: Optional[str] = None
    submitter_phone: Optional[str] = None


class FeedbackPayload(BaseModel):
    is_anonymous: bool
    feedback_content: str
    location: str
    card_number: Optional[str] = None
    full_name: Optional[str] = None
    phone_number: Optional[str] = None
    verification_code: str  # 新增验证码字段


class FeedbackInDB(BaseModel):
    # 原有的字段
    id: int
    is_anonymous: bool
    feedback_content: str
    location: str
    submission_timestamp: str
    status: str
    reader_id: Optional[int] = None
    image_filename: Optional[str] = None
    category1: Optional[str] = None
    category2: Optional[str] = None
    assigned_department: Optional[str] = None
    department_reply: Optional[str] = None
    reply_timestamp: Optional[str] = None

    # 新增：从 readers 表 join 来的字段，设为可选（因为匿名反馈没有这些信息）
    full_name: Optional[str] = None
    phone_number: Optional[str] = None


class AdminFeedbackUpdate(BaseModel):
    """管理员更新反馈时使用的模型（所有字段都是可选的）"""

    category1: Optional[str] = None
    category2: Optional[str] = None
    assigned_department: Optional[str] = None
    status: Optional[str] = None
    department_reply: Optional[str] = None


# --- API 路由 ---


@app.post(
    "/feedback",
    response_model=Dict[str, Any],
    status_code=status.HTTP_201_CREATED,
    summary="提交新的读者反馈",
)
async def submit_feedback(
    payload_str: str = Form(..., alias="payload"),
    image: Optional[UploadFile] = File(None),
):
    try:
        payload = FeedbackPayload.model_validate_json(payload_str)
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"JSON payload格式错误: {e}")
    # --- 新增的验证码校验逻辑 ---
    phone_to_verify = payload.phone_number
    if not phone_to_verify or phone_to_verify not in verification_codes:
        raise HTTPException(status_code=400, detail="请先获取验证码或手机号不匹配")
    stored_info = verification_codes[phone_to_verify]
    # 检查验证码是否正确，并且是否在有效期内（例如5分钟）
    if stored_info["code"] != payload.verification_code:
        raise HTTPException(status_code=400, detail="验证码错误")
    if time.time() - stored_info["timestamp"] > 300:  # 300秒 = 5分钟
        raise HTTPException(status_code=400, detail="验证码已过期，请重新获取")
    # 验证成功后，可以从存储中删除，防止重复使用
    del verification_codes[phone_to_verify]

    reader_id = None  # 初始化 reader_id
    # --- 根据是否匿名，处理读者信息 ---
    if not payload.is_anonymous:
        # 如果是实名提交，必须提供 card_number, full_name 和 phone_number
        if not all([payload.card_number, payload.full_name, payload.phone_number]):
            raise HTTPException(
                status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                detail="实名反馈时,电话为必填项。",
            )

        conn_for_reader = None
        try:
            conn_for_reader = db.get_db_connection()
            cur = conn_for_reader.cursor()

            # 查找或创建读者
            cur.execute(
                "SELECT id FROM readers WHERE card_number = %s", (payload.card_number,)
            )
            reader_record = cur.fetchone()

            if reader_record:
                reader_id = reader_record[0]
                cur.execute(
                    "UPDATE readers SET full_name = %s, phone_number = %s WHERE id = %s",
                    (payload.full_name, payload.phone_number, reader_id),
                )
            else:
                cur.execute(
                    "INSERT INTO readers (card_number, full_name, phone_number) VALUES (%s, %s, %s) RETURNING id",
                    (payload.card_number, payload.full_name, payload.phone_number),
                )
                reader_id = cur.fetchone()[0]

            conn_for_reader.commit()
            cur.close()
        except Exception as e:
            if conn_for_reader:
                conn_for_reader.rollback()
            raise HTTPException(status_code=500, detail=f"处理读者信息时出错: {e}")
        finally:
            if conn_for_reader:
                conn_for_reader.close()

    saved_filename = None
    if image and image.filename:
        # --- 文件处理逻辑 ---
        # 1. 为防止文件名冲突和安全问题，生成一个安全、唯一的文件名
        file_extension = os.path.splitext(image.filename)[1]
        # 使用 UUID v4 生成一个随机的文件名，保留原始扩展名
        saved_filename = f"{uuid.uuid4()}{file_extension}"
        file_path = os.path.join(UPLOADS_DIR, saved_filename)

        # 2. 异步地将上传的文件内容写入到服务器磁盘
        try:
            async with aiofiles.open(file_path, "wb") as f:
                while content := await image.read(1024):  # 每次读取 1KB
                    await f.write(content)
        except Exception as e:
            raise HTTPException(status_code=500, detail=f"文件保存失败: {e}")

    # --- 数据库操作逻辑 ---
    conn = None
    try:
        conn = db.get_db_connection()
        cur = conn.cursor()
        cur.execute(
            """
            INSERT INTO feedback_submissions
            (is_anonymous, feedback_content, location, image_filename, reader_id, status)
            VALUES (%s, %s, %s, %s, %s, %s) RETURNING id;
            """,
            (
                payload.is_anonymous,
                payload.feedback_content,
                payload.location,
                saved_filename,
                reader_id,
                "已提交",
            ),
        )
        feedback_id = cur.fetchone()[0]
        conn.commit()
        return {"message": "反馈提交成功", "feedback_id": feedback_id}
    except Exception as e:
        if conn:
            conn.rollback()
        raise HTTPException(status_code=500, detail=f"数据库操作失败: {e}")
    finally:
        if conn:
            conn.close()


@app.get(
    "/feedback/status/{feedback_id}",
    response_model=Dict[str, Any],
    summary="查询指定反馈的处理状态",
)
def get_feedback_status(feedback_id: int):
    """
    根据反馈 ID 查询其处理状态和回复。
    - **feedback_id**: 要查询的反馈的唯一ID。
    """
    conn = None
    try:
        conn = db.get_db_connection()
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            "SELECT id, status, submission_timestamp, department_reply, reply_timestamp FROM feedback_submissions WHERE id = %s;",
            (feedback_id,),
        )
        record = cur.fetchone()
        if not record:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND, detail="未找到该反馈记录"
            )

        # 将 datetime 对象转换为 ISO 格式字符串
        for key, value in record.items():
            if hasattr(value, "isoformat"):
                record[key] = value.isoformat()

        return record
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"服务器内部错误: {e}",
        )
    finally:
        if conn:
            conn.close()


@app.get(
    "/admin/feedback",
    response_model=List[FeedbackInDB],
    summary="获取所有读者反馈列表 (管理端)",
)
def get_all_feedback():
    """
    获取所有反馈记录，并通过 LEFT JOIN 关联读者信息。
    """
    conn = None
    try:
        conn = db.get_db_connection()
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        # --- 核心改动：使用 LEFT JOIN 查询 ---
        sql_query = """
                    SELECT fs.*, \
                           r.full_name, \
                           r.phone_number
                    FROM feedback_submissions fs \
                             LEFT JOIN \
                         readers r ON fs.reader_id = r.id
                    ORDER BY fs.submission_timestamp DESC; \
                    """

        cur.execute(sql_query)
        records = cur.fetchall()

        # 将 datetime 对象转换为 ISO 格式字符串
        for record in records:
            for key, value in record.items():
                if hasattr(value, "isoformat"):
                    record[key] = value.isoformat()

        return records
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"服务器内部错误: {e}",
        )
    finally:
        if conn:
            conn.close()


@app.post(
    "/admin/feedback/{feedback_id}",
    response_model=Dict[str, Any],
    summary="更新一条反馈记录 (管理端)",
)
def update_feedback(feedback_id: int, update_data: AdminFeedbackUpdate):
    """
    根据反馈 ID 更新其分类、状态或添加回复。
    - **feedback_id**: 要更新的反馈的唯一ID。
    - **update_data**: 包含要更新字段的请求体。
    """
    # .dict(exclude_unset=True) 是一个强大的功能，它只包含用户在请求中实际发送的字段
    update_fields = update_data.dict(exclude_unset=True)

    if not update_fields:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="没有提供需要更新的字段"
        )

    set_clause = ", ".join([f"{key} = %s" for key in update_fields.keys()])
    values = list(update_fields.values())

    if "department_reply" in update_fields and update_fields["department_reply"]:
        set_clause += ", reply_timestamp = NOW()"

    sql_query = f"UPDATE feedback_submissions SET {set_clause}, updated_at = NOW() WHERE id = %s RETURNING id;"
    values.append(feedback_id)

    conn = None
    try:
        conn = db.get_db_connection()
        cur = conn.cursor()
        cur.execute(sql_query, tuple(values))
        if cur.fetchone():
            conn.commit()
            return {"message": "反馈更新成功", "feedback_id": feedback_id}
        else:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND, detail="未找到该反馈记录"
            )
    except Exception as e:
        if conn:
            conn.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"服务器内部错误: {e}",
        )
    finally:
        if conn:
            conn.close()


@app.get(
    "/readers/{reader_id}/feedback",
    response_model=List[FeedbackInDB],  # 响应模型是反馈列表
    summary="获取指定读者的所有反馈历史",
)
def get_feedback_for_reader(reader_id: str):
    """
    根据读者 ID，获取该读者提交过的所有反馈记录，按时间倒序排列。
    """
    conn = None
    try:
        conn = db.get_db_connection()
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        # SQL 查询，通过 WHERE reader_id = %s 来筛选
        sql_query = """
                    SELECT fs.*, \
                           r.full_name, \
                           r.phone_number
                    FROM feedback_submissions fs \
                             INNER  JOIN \
                         readers r ON fs.reader_id = r.id
                    WHERE r.card_number = %s
                    ORDER BY fs.submission_timestamp DESC; \
                    """

        cur.execute(sql_query, (reader_id,))
        records = cur.fetchall()

        # 转换时间戳为字符串
        for record in records:
            for key, value in record.items():
                if hasattr(value, "isoformat"):
                    record[key] = value.isoformat()

        return records
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"服务器内部错误: {e}",
        )
    finally:
        if conn:
            conn.close()


# --- 新增：专门用于发送验证码的 API 接口 ---


@app.post("/send-sms-code", status_code=status.HTTP_200_OK, summary="发送手机验证码")
def request_sms_code(payload: SmsRequest):
    phone = payload.phone_number
    # 1. 生成一个随机的6位验证码
    code = f"{random.randint(0, 999999):06d}"

    # 2. 发送短信
    success, message = send_sms(phone, code)

    if not success:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"短信发送失败: {message}",
        )

    # 3. 临时存储验证码，并记录时间戳（用于设置有效期）
    verification_codes[phone] = {"code": code, "timestamp": time.time()}

    return {"message": "验证码已发送，请注意查收。"}
